package org.sqlproc.engine;

import java.util.HashSet;
import java.util.List;

import org.hibernate.Session;
import org.hibernate.Transaction;
import org.sqlproc.engine.SqlEngine;
import org.sqlproc.engine.form.LibraryTransport;
import org.sqlproc.engine.form.MediaTransport;
import org.sqlproc.engine.form.SearchForm;

public class CommonTestAdvanced extends TestDatabase {

    protected String getDataSetFile() {
        return "dbunit/AdvancedTest.xml";
    }

    public void testConditionalJoinAdv(boolean newGrammar) {
        Session session = sessionFactory.openSession();
        Transaction tx = session.beginTransaction();

        SqlEngine sqlEngine = getSqlEngine("CONDITIONAL_JOIN_ADV");

        SearchForm sf = new SearchForm();

        String sql = sqlEngine.getSql(sf, null, SqlEngine.NO_ORDER);
        logger.info(sql);
        assertTrue(sql.contains("m.id, m.TITLE, e.ROLE  , '' first, '' last"));

        List<MediaTransport> list = sqlEngine.query(session, MediaTransport.class, sf);

        assertEquals(3, list.size());
        MediaTransport gt = list.get(0);
        assertEquals(new Long(1), gt.getId());
        assertEquals("Pippi Långstrump i Söderhavet", gt.getMediaTitle());
        assertEquals("", gt.getEngagement().getFirst());
        assertEquals("", gt.getEngagement().getLast());
        assertEquals(null, gt.getEngagement().getRole());

        sf = new SearchForm();
        sf.setIdSet(new HashSet<Long>());
        sf.getIdSet().add(2L);
        sf.getIdSet().add(3L);
        sf.setFname("Pierce");

        sql = sqlEngine.getSql(sf, null, SqlEngine.NO_ORDER);
        logger.info(sql);
        assertTrue(sql.contains("m.id, m.TITLE, e.ROLE  , p.NAME_FIRST first, p.NAME_LAST last"));

        list = sqlEngine.query(session, MediaTransport.class, sf);

        assertEquals(1, list.size());
        gt = list.get(0);
        assertEquals(new Long(2), gt.getId());
        assertEquals("Die Another Day", gt.getMediaTitle());
        assertEquals("Pierce", gt.getEngagement().getFirst());
        assertEquals("Brosnan", gt.getEngagement().getLast());
        assertEquals("Actor", gt.getEngagement().getRole());

        tx.commit();
        session.close();
    }

    public void testConditionalJoin2LevelsAdv(boolean newGrammar) {
        Session session = sessionFactory.openSession();
        Transaction tx = session.beginTransaction();

        SqlEngine sqlEngine = getSqlEngine("CONDITIONAL_JOIN_2_LEVELS_ADV");

        SearchForm sf = new SearchForm();

        String sql = sqlEngine.getSql(sf, null, SqlEngine.NO_ORDER);
        logger.info(sql);
        assertTrue(sql.contains("l.id, l.NAME, pm.location   , '' title, '' role, '' first, '' last"));

        List<LibraryTransport> list = sqlEngine.query(session, LibraryTransport.class, sf);

        assertEquals(2, list.size());
        LibraryTransport gt = list.get(0);
        assertEquals(new Long(1), gt.getId());
        assertEquals("LibraryServiceTest", gt.getName());
        assertEquals("abc123", gt.getLocation());

        sf = new SearchForm();
        sf.setMedia("Die Another Day");

        sql = sqlEngine.getSql(sf, null, SqlEngine.NO_ORDER);
        logger.info(sql);
        assertTrue(sql.contains("l.id, l.NAME, pm.location   , m.TITLE, e.ROLE, '' first, '' last"));

        list = sqlEngine.query(session, LibraryTransport.class, sf);

        assertEquals(1, list.size());
        gt = list.get(0);
        assertEquals(new Long(1), gt.getId());
        assertEquals("LibraryServiceTest", gt.getName());
        assertEquals("abc456", gt.getLocation());
        assertEquals("Die Another Day", gt.getMedia().getMediaTitle());
        assertEquals("Actor", gt.getMedia().getEngagement().getRole());

        sf = new SearchForm();
        sf.setMedia("Die Another Day");
        sf.setFname("Pierce");

        sql = sqlEngine.getSql(sf, null, SqlEngine.NO_ORDER);
        logger.info(sql);
        assertTrue(sql.contains("l.id, l.NAME, pm.location   , m.TITLE, e.ROLE, p.NAME_FIRST first, p.NAME_LAST last"));

        list = sqlEngine.query(session, LibraryTransport.class, sf);

        assertEquals(1, list.size());
        gt = list.get(0);
        assertEquals(new Long(1), gt.getId());
        assertEquals("LibraryServiceTest", gt.getName());
        assertEquals("abc456", gt.getLocation());
        assertEquals("Die Another Day", gt.getMedia().getMediaTitle());
        assertEquals("Actor", gt.getMedia().getEngagement().getRole());
        assertEquals("Pierce", gt.getMedia().getEngagement().getFirst());
        assertEquals("Brosnan", gt.getMedia().getEngagement().getLast());

        sf = new SearchForm();
        sf.setFname("Pierce");

        sql = sqlEngine.getSql(sf, null, SqlEngine.NO_ORDER);
        logger.info(sql);
        assertTrue(sql.contains("l.id, l.NAME, pm.location   , '' title, '' role, '' first, '' last"));

        list = sqlEngine.query(session, LibraryTransport.class, sf);

        assertEquals(2, list.size());
        gt = list.get(0);
        assertEquals(new Long(1), gt.getId());
        assertEquals("LibraryServiceTest", gt.getName());
        assertEquals("abc123", gt.getLocation());
        assertEquals("", gt.getMedia().getMediaTitle());
        assertEquals("", gt.getMedia().getEngagement().getRole());
        assertEquals("", gt.getMedia().getEngagement().getFirst());
        assertEquals("", gt.getMedia().getEngagement().getLast());

        tx.commit();
        session.close();
    }

    public void testConditionalWhereAdv(boolean newGrammar) {
        Session session = sessionFactory.openSession();
        Transaction tx = session.beginTransaction();

        SqlEngine sqlEngine = getSqlEngine("CONDITIONAL_WHERE_ADV");

        SearchForm sf = new SearchForm();

        String sql = sqlEngine.getSql(sf, null, SqlEngine.NO_ORDER);
        logger.info(sql);
        assertTrue(sql.contains("m.id, m.TITLE, e.ROLE  , '' first, '' last"));
        assertTrue(sql.contains("OR  p.NAME_FIRST is null"));

        List<MediaTransport> list = sqlEngine.query(session, MediaTransport.class, sf);

        assertEquals(3, list.size());
        MediaTransport gt = list.get(0);
        assertEquals(new Long(1), gt.getId());
        assertEquals("Pippi Långstrump i Söderhavet", gt.getMediaTitle());
        assertEquals("", gt.getEngagement().getFirst());
        assertEquals("", gt.getEngagement().getLast());
        assertEquals(null, gt.getEngagement().getRole());

        sf = new SearchForm();
        sf.setIdSet(new HashSet<Long>());
        sf.getIdSet().add(2L);
        sf.getIdSet().add(3L);
        sf.setFname("Pierce");

        sql = sqlEngine.getSql(sf, null, SqlEngine.NO_ORDER);
        logger.info(sql);
        assertTrue(sql.contains("m.id, m.TITLE, e.ROLE  , p.NAME_FIRST first, p.NAME_LAST last"));
        assertTrue(sql.contains("AND  UPPER(p.NAME_FIRST) = :fname"));

        list = sqlEngine.query(session, MediaTransport.class, sf);

        assertEquals(1, list.size());
        gt = list.get(0);
        assertEquals(new Long(2), gt.getId());
        assertEquals("Die Another Day", gt.getMediaTitle());
        assertEquals("Pierce", gt.getEngagement().getFirst());
        assertEquals("Brosnan", gt.getEngagement().getLast());
        assertEquals("Actor", gt.getEngagement().getRole());

        tx.commit();
        session.close();
    }

    public void testConditionalWhereAndBracesAdv(boolean newGrammar) {
        Session session = sessionFactory.openSession();
        Transaction tx = session.beginTransaction();

        SqlEngine sqlEngine = getSqlEngine("CONDITIONAL_WHERE_AND_BRACES_ADV");

        SearchForm sf = new SearchForm();

        String sql = sqlEngine.getSql(sf, null, SqlEngine.NO_ORDER);
        logger.info(sql);
        assertTrue(sql.contains("m.id, m.TITLE, e.ROLE  , '' first, '' last"));
        assertTrue(sql.contains("1=1"));

        List<MediaTransport> list = sqlEngine.query(session, MediaTransport.class, sf);

        assertEquals(3, list.size());
        MediaTransport gt = list.get(0);
        assertEquals(new Long(1), gt.getId());
        assertEquals("Pippi Långstrump i Söderhavet", gt.getMediaTitle());
        assertEquals("", gt.getEngagement().getFirst());
        assertEquals("", gt.getEngagement().getLast());
        assertEquals(null, gt.getEngagement().getRole());

        sf = new SearchForm();
        sf.setFname("Pierce");

        sql = sqlEngine.getSql(sf, null, SqlEngine.NO_ORDER);
        logger.info(sql);
        assertTrue(sql.contains("m.id, m.TITLE, e.ROLE  , p.NAME_FIRST first, p.NAME_LAST last"));
        assertTrue(sql.contains("1=1   AND   UPPER(p.NAME_FIRST) = :fname"));

        list = sqlEngine.query(session, MediaTransport.class, sf);

        assertEquals(1, list.size());
        gt = list.get(0);
        assertEquals(new Long(2), gt.getId());
        assertEquals("Die Another Day", gt.getMediaTitle());
        assertEquals("Pierce", gt.getEngagement().getFirst());
        assertEquals("Brosnan", gt.getEngagement().getLast());
        assertEquals("Actor", gt.getEngagement().getRole());

        sf = new SearchForm();
        sf.setFname("Pierce");
        sf.setLname("Brosnan");

        sql = sqlEngine.getSql(sf, null, SqlEngine.NO_ORDER);
        logger.info(sql);
        assertTrue(sql.contains("m.id, m.TITLE, e.ROLE  , p.NAME_FIRST first, p.NAME_LAST last"));
        assertTrue(sql.contains("UPPER(p.NAME_LAST) = :lname    AND    UPPER(p.NAME_FIRST) = :fname"));

        list = sqlEngine.query(session, MediaTransport.class, sf);

        assertEquals(1, list.size());
        gt = list.get(0);
        assertEquals(new Long(2), gt.getId());
        assertEquals("Die Another Day", gt.getMediaTitle());
        assertEquals("Pierce", gt.getEngagement().getFirst());
        assertEquals("Brosnan", gt.getEngagement().getLast());
        assertEquals("Actor", gt.getEngagement().getRole());

        sf = new SearchForm();
        sf.setLname("Brosnan");

        sql = sqlEngine.getSql(sf, null, SqlEngine.NO_ORDER);
        logger.info(sql);
        assertTrue(sql.contains("m.id, m.TITLE, e.ROLE  , p.NAME_FIRST first, p.NAME_LAST last"));
        assertTrue(sql.contains("UPPER(p.NAME_LAST) = :lname    AND  1=1"));

        list = sqlEngine.query(session, MediaTransport.class, sf);

        assertEquals(1, list.size());
        gt = list.get(0);
        assertEquals(new Long(2), gt.getId());
        assertEquals("Die Another Day", gt.getMediaTitle());
        assertEquals("Pierce", gt.getEngagement().getFirst());
        assertEquals("Brosnan", gt.getEngagement().getLast());
        assertEquals("Actor", gt.getEngagement().getRole());

        tx.commit();
        session.close();
    }

    public void testConditionalWhereAndBracesMoreAdv(boolean newGrammar) {
        Session session = sessionFactory.openSession();
        Transaction tx = session.beginTransaction();

        SqlEngine sqlEngine = getSqlEngine("CONDITIONAL_WHERE_AND_BRACES_MORE_ADV");

        SearchForm sf = new SearchForm();
        sf.setFname("Pierce");

        String sql = sqlEngine.getSql(sf, null, SqlEngine.NO_ORDER);
        logger.info(sql);
        assertTrue(sql.contains("m.id, m.TITLE, e.ROLE  , '' first, '' last, '' ssn"));
        assertTrue(sql.contains("1=1"));

        List<MediaTransport> list = sqlEngine.query(session, MediaTransport.class, sf);

        assertEquals(3, list.size());
        MediaTransport gt = list.get(0);
        assertEquals(new Long(1), gt.getId());
        assertEquals("Pippi Långstrump i Söderhavet", gt.getMediaTitle());
        assertEquals("", gt.getEngagement().getFirst());
        assertEquals("", gt.getEngagement().getLast());
        assertEquals("", gt.getEngagement().getSsn());
        assertEquals(null, gt.getEngagement().getRole());

        sf = new SearchForm();
        sf.setFname("Pierce");
        sf.setLname("Brosnan");

        sql = sqlEngine.getSql(sf, null, SqlEngine.NO_ORDER);
        logger.info(sql);
        assertTrue(sql.contains("m.id, m.TITLE, e.ROLE  , p.NAME_FIRST first, p.NAME_LAST last, p.SSN_NUMBER ssn"));
        assertTrue(sql.contains("1=1  AND UPPER(p.NAME_LAST) = :lname AND UPPER(p.NAME_FIRST) = :fname"));

        list = sqlEngine.query(session, MediaTransport.class, sf);

        assertEquals(1, list.size());
        gt = list.get(0);
        assertEquals(new Long(2), gt.getId());
        assertEquals("Die Another Day", gt.getMediaTitle());
        assertEquals("Pierce", gt.getEngagement().getFirst());
        assertEquals("Brosnan", gt.getEngagement().getLast());
        assertEquals("Actor", gt.getEngagement().getRole());
        assertEquals("123456", gt.getEngagement().getSsn());

        sf = new SearchForm();
        sf.setSsn("123456");

        sql = sqlEngine.getSql(sf, null, SqlEngine.NO_ORDER);
        logger.info(sql);
        assertTrue(sql.contains("m.id, m.TITLE, e.ROLE  , p.NAME_FIRST first, p.NAME_LAST last, p.SSN_NUMBER ssn"));
        assertTrue(sql.contains("1=1   AND  p.SSN = :ssn"));

        list = sqlEngine.query(session, MediaTransport.class, sf);

        assertEquals(1, list.size());
        gt = list.get(0);
        assertEquals(new Long(2), gt.getId());
        assertEquals("Die Another Day", gt.getMediaTitle());
        assertEquals("Pierce", gt.getEngagement().getFirst());
        assertEquals("Brosnan", gt.getEngagement().getLast());
        assertEquals("Actor", gt.getEngagement().getRole());
        assertEquals("123456", gt.getEngagement().getSsn());

        sf = new SearchForm();
        sf.setFname("Pierce");
        sf.setLname("Brosnan");
        sf.setSsn("123456");

        sql = sqlEngine.getSql(sf, null, SqlEngine.NO_ORDER);
        logger.info(sql);
        assertTrue(sql.contains("m.id, m.TITLE, e.ROLE  , p.NAME_FIRST first, p.NAME_LAST last, p.SSN_NUMBER ssn"));
        assertTrue(sql
                .contains("1=1  AND UPPER(p.NAME_LAST) = :lname AND UPPER(p.NAME_FIRST) = :fname  AND  p.SSN = :ssn"));

        list = sqlEngine.query(session, MediaTransport.class, sf);

        assertEquals(1, list.size());
        gt = list.get(0);
        assertEquals(new Long(2), gt.getId());
        assertEquals("Die Another Day", gt.getMediaTitle());
        assertEquals("Pierce", gt.getEngagement().getFirst());
        assertEquals("Brosnan", gt.getEngagement().getLast());
        assertEquals("Actor", gt.getEngagement().getRole());
        assertEquals("123456", gt.getEngagement().getSsn());

        tx.commit();
        session.close();
    }
}
